import json
import pymysql

conn = pymysql.connect(host="localhost", user="root",
                                       password="root", db="nba", port=3306, charset='utf8')
                # 使用cursor()方法获取操作游标
cur = conn.cursor()
nbayears = range(1996,2018)
game_types = [1,2,4]
for nbayear in nbayears:
    for game_type in game_types:
        filename = '球员' + str(nbayear) + '年' + str(game_type) + '类型数据.json'
        try:
            with open(filename) as f:
                contents = json.loads(f.read())
        except:
            print('无此文件！')
        else:
            players = contents['players']
            for player in players:
                #1.获取球员id，方便数据库查询
                pro = player['playerProfile']
                dataid = [pro.get('playerId')]
                #2.获取球员数据
                stat = player['statAverage']
                dataaver = [str(nbayear),str(game_type),stat.get('games'),stat.get('gamesStarted'),
                            stat.get('rebsPg'),stat.get('assistsPg'),stat.get('minsPg'),
                            stat.get('efficiency'),stat.get('fgpct'),stat.get('tppct'),
                            stat.get('ftpct'),stat.get('offRebsPg'),stat.get('defRebsPg'),
                            stat.get('stealsPg'),stat.get('blocksPg'),stat.get('turnoversPg'),
                            stat.get('foulsPg'),stat.get('pointsPg')]
                total = dataaver + dataid
                print(total)
                sql = "INSERT INTO player_data(nbayear,gametype,games,gamesStarted,rebsPg,assistsPg,minsPg,efficiency,fgpct,tppct,ftpct,offRebsPg,defRebsPg,stealsPg,blocksPg,turnoversPg,foulsPg,pointsPg,playerid) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                effect = cur.execute(sql, total)
                conn.commit()
print('添加数据库成功！')
# 关闭游标
cur.close()
# 关闭连接
conn.close()

